package DBLayer;

import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBStore implements IFDBStore {
	private Connection con;

	// this method tries to connect to the database
	public DBStore() {
		con = DBConnection.getInstance().getDBcon();
	}

	// this method lists all stores
	public ArrayList<Store> listAllStores(boolean retriveAssociation) {
		return miscWhere("");
	}

	// this method finds a store on its phonenumber
	public Store findStore(int phoneNumber, boolean retriveAssociation) {
		String wClause = "  phonenumber = '" + phoneNumber + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	// this method inserts a new store into the database
	public int insertStore(Store sto) throws Exception {
		int rc = -1;
		String query = "INSERT INTO Store(phoneNumber, adress, zipcode, email)  VALUES('"
				+ sto.getPhoneNumber()
				+ "','"
				+ sto.getAdress()
				+ "','"
				+ sto.getZipCode() + "','" + sto.getEmail() + "','";

		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex) {
			System.out.println("Store ikke oprettet");
			throw new Exception("Store is not inserted correct");
		}
		return (rc);
	}

	// this method deletes a ore with the given phonenumber
	public int deleteStore(int phoneNumber) {
		int rc = -1;

		String query = "DELETE FROM Store WHERE phoneNumber = '" + phoneNumber
				+ "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in Store db: " + ex);
		}
		return (rc);
	}

	// this method updates a store object
	public int updateStore(Store sto) {
		Store stoObj = sto;
		int rc = -1;

		String query = "UPDATE Store SET " + "phoneNumber ='"
				+ stoObj.getPhoneNumber() + "', " + "adress ='"
				+ stoObj.getAdress() + "', " + "zipCode ='"
				+ stoObj.getZipCode() + "', " + "email = '" + stoObj.getEmail()
				+ "',";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		} catch (Exception ex) {
			System.out.println("Update exception in Store db: " + ex);
		}
		return (rc);
	}

	// private methods
	// michWere is used whenever we want to select more than one Supplier

	private ArrayList<Store> miscWhere(String wClause) {
		ResultSet results;
		ArrayList<Store> list = new ArrayList<Store>();

		String query = buildQuery(wClause);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Store stoObj = new Store();
				stoObj = buildStore(results);
				list.add(stoObj);
			}// end while
			stmt.close();
			
		}// slut try
		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	// Singelwhere is used when we only select one store
	private Store singleWhere(String wClause, boolean retriveAssociation) {
		ResultSet results;
		Store stoObj = new Store();

		String query = buildQuery(wClause);
		System.out.println(query);
		try { // read the Supplier from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				stoObj = buildStore(results);
				// assocaition is to be build
				stmt.close();

			} 
			
			else {
				stoObj = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return stoObj;
	}

	// this method builds a new query
	private String buildQuery(String wClause) {
		String query = "SELECT *  FROM Supplier";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	// this method builds a new store
	private Store buildStore(ResultSet results) {
		Store stoObj = new Store();
		try { // the columns from the table store are used
			stoObj.setPhoneNumber(results.getInt("phoneNumber"));
			stoObj.setAdress(results.getString("adress"));
			stoObj.setZipCode(results.getInt("zipCode"));
			stoObj.setEmail(results.getString("email"));
		} catch (Exception e) {
			System.out.println("error in building the Store object");
		}
		return stoObj;
	}

}